﻿CREATE PROCEDURE [dbo].[magazines_listing]
    @page int = 0,
    @size int = 0,
    @column nvarchar(256),
    @direction int=0,
    @name nvarchar(max) = null
AS
    SELECT 
        Magazine.MagazineId,
        Magazine.Name
    FROM
        Magazine
    WHERE
        (@name IS NULL OR Magazine.Name LIKE '%' + @name + '%' )
    ORDER BY 
        CASE WHEN @direction = 1 and @column = 'Name' THEN Magazine.Name END DESC,
        CASE WHEN @direction = 0 and @column = 'Name' THEN Magazine.Name END ASC

    OFFSET((@page-1)*@size) ROWS
    FETCH NEXT @size ROWS ONLY

    Declare @TotalCount int = 0

    Select @TotalCount = COUNT(1)
    FROM Magazine
    WHERE
        (@name IS NULL OR Magazine.Name LIKE '%' + @name + '%')
RETURN @TotalCount
